package com.bwda.dsrs.filecenter.service.impl;

import com.bwda.dsrs.base.util.AesEncryptPaddingUtil;
import com.bwda.dsrs.base.util.StringUtil;
import com.bwda.dsrs.filecenter.domain.po.dbinfo.DbInfoPo;
import com.bwda.dsrs.filecenter.domain.po.dbscanresult.DbScanResultPo;
import com.bwda.dsrs.filecenter.service.IRemoteService;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;


import java.sql.*;
import java.util.Date;
import java.util.*;
/**
 * @author Administrator
 */
@Service
public class OracleRemoteServiceImpl implements IRemoteService {

    @Value("${aes_key}")
    private String aesKey;

    @Value("${oracle_builtIn_user}")
    private String oracleBuiltInUser;


    private final Logger logger = LoggerFactory.getLogger(OracleRemoteServiceImpl.class);

    @Override
    public Connection login(DbInfoPo dbInfoPo) throws Exception {
        StringBuffer url = new StringBuffer();
        url.append("jdbc:oracle:thin:@//").append(dbInfoPo.getDbIp());
        url.append(":").append(dbInfoPo.getDbPort());
        url.append("/").append(dbInfoPo.getServiceName());
        String driverName = "oracle.jdbc.driver.OracleDriver";
        Properties conProps = new Properties();
        conProps.put("user", dbInfoPo.getDbAccount());
        String pwdDecryp = AesEncryptPaddingUtil.decrypt(dbInfoPo.getDbPassword(), aesKey);
        logger.info("ip{}的{}数据库", dbInfoPo.getDbIp(), dbInfoPo.getServiceName());
        conProps.put("password", pwdDecryp);
        conProps.put("remarksReporting", "true");
//        if (dbInfoPo.getDbAccount().equalsIgnoreCase("sys")) {
//            conProps.put("defaultRowPrefetch", "15");
//            conProps.put("internal_logon", "sysdba");
//        }
        Class.forName(driverName);
        DriverManager.setLoginTimeout(10);
        return DriverManager.getConnection(url.toString(), conProps);
    }

    @Override
    public List<Map<String,Object>> getData(Connection connection, String tableName, String columnName, Integer rowNum, Integer sumRowNum) throws Exception {
        String sql;
        if(StringUtils.isBlank(columnName)){
            if(rowNum!=null && rowNum>0 && (sumRowNum-rowNum)>0){
//            sql = "select t2."+"\""+columnName.toUpperCase()+"\""+" from (select t1."+"\""+columnName.toUpperCase()+"\""+", rownum as rn from "+tableName+" t1 where rownum <= "+sumRowNum+
//                    ")t2 where t2.rn > "+(sumRowNum-rowNum);//最新数据
                sql = "select * from "+tableName+" where rownum<= "+ rowNum;
            }
            else {
                sql = "select * from "+tableName;
            }
        }
        else {
            if(rowNum!=null && rowNum>0 && (sumRowNum-rowNum)>0){
//            sql = "select t2."+"\""+columnName.toUpperCase()+"\""+" from (select t1."+"\""+columnName.toUpperCase()+"\""+", rownum as rn from "+tableName+" t1 where rownum <= "+sumRowNum+
//                    ")t2 where t2.rn > "+(sumRowNum-rowNum);//最新数据
                sql = "select "+"\""+columnName.toUpperCase()+"\""+" from "+tableName+" where rownum<= "+ rowNum;
            }
            else {
                sql = "select "+"\""+columnName.toUpperCase()+"\""+" from "+tableName;
            }
        }
        Statement stmt = null;
        ResultSet rs = null;
        logger.info("getData will run sql = {}", sql);
//        List<Object> res = new ArrayList<>();
        List<Map<String,Object>> res = new ArrayList<>();
        try {
            stmt = connection.createStatement();
            rs = stmt.executeQuery(sql);
            //获取键名
            ResultSetMetaData md = rs.getMetaData();
            //获取行的数量
            int columnCount = md.getColumnCount();
//            while (rs.next()) {
//                for (int i = 1; i <= columnCount; i++) {
//                    res.add(rs.getObject(i));
//                }
//            }
            while (rs.next()) {
                Map<String,Object> rowData = new HashMap<String,Object>();//声明Map
                for (int i = 1; i <= columnCount; i++) {
                    rowData.put(md.getColumnLabel(i).toLowerCase(), rs.getObject(i));//获取键名及值
                }
                res.add(rowData);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }

        }
        return res;
//        return JSONObject.parseArray(JSON.toJSONString(res),DbInfoPo.class);
    }

    @Override
    public List<String> getAllDBList(Connection connection, DbInfoPo dbInfoPo) throws Exception {
        ResultSet rs = connection.getMetaData().getSchemas();
        List<String> stringList = new ArrayList<>();
        while (rs.next()) {
            String tableSchem = rs.getString("TABLE_SCHEM");
            stringList.add(tableSchem);
        }
        rs.close();
        return stringList;
    }

    @Override
    public List<String> getAllTableList(Connection connection,String dbName) throws Exception {
        List<String> stringList = new ArrayList<>();
        /*String[] types = {"TABLE"};
        ResultSet rs = connection.getMetaData().getTables(null, null, "%", types);
       //获取配置文件中oracle自带的用户
        List<String> adminList = new ArrayList<>();
        String[] sourceStrArray = oracleBuiltInUser.split(",");
        for (int i = 0; i < sourceStrArray.length; i++) {
            adminList.add(sourceStrArray[i].trim());
        }
        while (rs.next()) {
            String uesrName = rs.getString(2);
            if (!adminList.stream().anyMatch(s -> StringUtils.endsWithIgnoreCase(s, uesrName))) {
                // 表名
                String tableName = rs.getString("TABLE_NAME");
                if (!tableName.contains("$")) {
                    stringList.add(uesrName.concat(".").concat(tableName));
                }
            }
        }*/
        String querySql = "select TABLE_NAME from user_tables";
        ResultSet rs = connection.createStatement().executeQuery(querySql);
        while(rs.next()){
            String tableName = rs.getString("TABLE_NAME");
            stringList.add(dbName.toUpperCase().concat(".").concat(tableName));

        }
        rs.close();
        return stringList;
    }

    @Override
    public List<DbScanResultPo> getTableColumns(Connection connection, String tableName) throws Exception {
        ResultSet rs = connection.getMetaData().getColumns(null, tableName.split("\\.")[0], tableName.split("\\.")[1], "%");
        List<DbScanResultPo> dbScanResultPos = new ArrayList<>();
        while (rs.next()) {
            DbScanResultPo dbScanResultPo = new DbScanResultPo();
            dbScanResultPo.setWordIds("");
            dbScanResultPo.setScanNums(0);
            dbScanResultPo.setMatchNums(0);
            dbScanResultPo.setCreateTime(new Date());
            dbScanResultPo.setUpdateTime(new Date());
            dbScanResultPo.setRemark("");
            dbScanResultPo.setDelFlag(0);
//            dbScanResultPo.setTableRemark(rs.getString("TABLE_SCHEM").toLowerCase());
//            dbScanResultPo.setTableName(rs.getString("TABLE_NAME").toLowerCase());
            dbScanResultPo.setTableName(tableName.toLowerCase());
            dbScanResultPo.setColumnRemark(StringUtils.isEmpty(rs.getString("REMARKS")) ? "" : rs.getString("REMARKS").toLowerCase());
            dbScanResultPo.setColumnName(rs.getString("COLUMN_NAME").toLowerCase());
            dbScanResultPo.setColumnType(rs.getString("TYPE_NAME").toLowerCase());
            dbScanResultPos.add(dbScanResultPo);
//            String tableSchemaName = rs.getString("TABLE_SCHEM");// 表的架构（可能为空）
//            String tableName_ = rs.getString("TABLE_NAME");// 表名
//            String columnName = rs.getString("COLUMN_NAME");// 列名
//            System.err.println(tableSchemaName+'-'+tableName+'-'+columnName);
        }
        rs.close();
        return dbScanResultPos;
    }

    @Override
    public Integer countRowNum(Connection connection, String tableName) throws Exception {
        String sql = "select count(1) as rowcount from " + tableName;
        logger.info("countRowNum will run sql = {}", sql);
        List<Map<String, Object>> res = new ArrayList<>();
        Statement stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        int count = 0;
        while (rs.next()) {
            count = rs.getInt(1);
        }
//        int count = ((BigDecimal)rs.get(0).get("ROWCOUNT")).intValue();
        rs.close();
        stmt.close();
        return count;
    }

    @Override
    public Integer countTable(Connection connection,String dbName) throws Exception {
        String[] types = {"TABLE"};
        ResultSet rs = connection.getMetaData().getTables(null, dbName, "%", types);
        int count = 0;
        while (rs.next()) {
            count++;
//             count = rs.getRow();
        }
//        ((OracleResultSetImpl) rs).getValidRows()
        rs.close();
        return count;
    }

    @Override
    public Integer countView(Connection connection) throws Exception {
        String[] types = {"VIEW"};
        ResultSet rs = connection.getMetaData().getTables(null, null, "%", types);
        int count = 0;
        while (rs.next()) {
            count++;
        }
        rs.close();
        return count;
    }

    @Override
    public Integer countFunction(Connection connection) throws Exception {
        ResultSet rs = connection.getMetaData().getFunctions(null, null, "%");
        int count = 0;
        while (rs.next()) {
            count++;
        }
        rs.close();
        return count;
    }

    @Override
    public Integer countSequence(Connection connection) throws Exception {
        return null;
    }

    @Override
    public List<Object> getDataBases(DbInfoPo dbInfoPo) throws Exception {
        return null;
    }
}
